import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
//HSSF is for old xcel format
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

//XSSF is for Latest xcel.
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.junit.After;
import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.ie.InternetExplorerDriver;


public class DDF1 {
	int xRows, xCols; 
	public WebDriver driver;
	
	@Test
	public void myMainTest() throws Exception{
		//Local variables to store test data
		String oSearchResults, vTDID, vUrl, vSearchTerm, vEmail, vPswd, vSearchResults, vWrongEmailMsg, vWelcomeMsg, vExecute;
		long iWait=3000;
       
		// Read each set of Test Data
		String xlPath = "C:\\Selenium\\ScreenCast_Download\\Aug16thBatch\\YT_Playing Around.xlsx";
		String[][] myXL = readXL(xlPath, "Test Data");
		
		//see what has been read from the Excel
		for (int i=0; i<xRows; i++) {
			for (int j=0; j<xCols; j++){
				System.out.println(myXL[i][j]);
			}
		}
		
		// create a driver to run automation
		WebDriver driver = new InternetExplorerDriver();
			
		//Take values from each row in 2D Local array "myXL" and download them into local variables
		for (int k=1; k<7; k++){
			// identify the TD to execute
						vExecute = myXL[k][8];
						// Only execute if Y
						if (vExecute.equals("Y")){
			vTDID=myXL[k][0];
			vUrl=myXL[k][1];
			vSearchTerm=myXL[k][2];
			vEmail=myXL[k][3];
			vPswd=myXL[k][4];
			vSearchResults=myXL[k][5];
			vWrongEmailMsg=myXL[k][6];
			vWelcomeMsg=myXL[k][7];
			
			//Execute TC001
			//Navigate to youtube.com
			driver.navigate().to(vUrl);
			
			//Enter a search term - from Excel Data
		    driver.findElement(By.xpath("//input[@id='masthead-search-term']")).sendKeys(vSearchTerm);
			
		    // click the search button.
		       driver.findElement(By.xpath("//button[@id='search-btn']")).click();
		       Thread.sleep(iWait);
		       oSearchResults = driver.findElement(By.xpath("//p[@class='num-results']")).getText();
		       System.out.println("The # of rearches: "+ oSearchResults);
		       
		       //Verify that the results show
		       if (vSearchResults.trim().equals(oSearchResults.trim())){
					myXL[k][9] = "Pass"; 
					//myXL[k][10]= oSearchResults;
					System.out.println("PASSSSSSS");
				} 
		       
		       else {
		    	   	myXL[k][9] = "Fail";
		    	   	//myXL[k][10]= oSearchResults;
					System.out.println("FAILLLLLLLL");
				}		
}		       
		       
		
	 else {
		System.out.println("Row is " + k + " is skipped");
	}
		
	}
	System.out.println("Writing back into excel");
	writeXL("C:\\Selenium\\ScreenCast_Download\\Aug16thBatch\\Result.xls", "TD Results", myXL);
	System.out.println("Wrote back into excel");
	System.out.println("The driver is " + driver);
	driver.quit();
}
	@After
	public void myAfter(){
		System.out.println("myAfter : " + driver);
	//	driver.close();
	}
	
	// CUSTOM METHODS ARE HERE
	// Method to read XL
	public String[][] readXL(String sPath, String iSheet) throws Exception{

		String[][] xData;                                                                
		
		File myxl = new File(sPath);                                
		FileInputStream myStream = new FileInputStream(myxl);                                
		XSSFWorkbook myWB = new XSSFWorkbook(myStream);    
		//XSSFWorkbook myWB = new XSSFWorkbook(); 
		XSSFSheet mySheet = myWB.getSheet(iSheet);                                 
		xRows = mySheet.getLastRowNum()+1;                                
		xCols = mySheet.getRow(0).getLastCellNum();                                
		xData = new String[xRows][xCols];        
		for (int i = 0; i < xRows; i++) {                           
				XSSFRow row = mySheet.getRow(i);
				for (int j = 0; j < xCols; j++) {                               
					XSSFCell cell = row.getCell(j);                               
					String value = cellToString(cell);                               
					xData[i][j] = value;                               
					}        
				}                                      
		return xData;
	}
	
	
	// Method to write into an XL
		public void writeXL(String sPath, String iSheet, String[][] xData) throws Exception{

	    	File outFile = new File(sPath);
	        HSSFWorkbook wb = new HSSFWorkbook();
	        HSSFSheet osheet = wb.createSheet(iSheet);
	        int xR_TS = xData.length;
	        int xC_TS = xData[0].length;
	    	for (int myrow = 0; myrow < xR_TS; myrow++) {
		        HSSFRow row = osheet.createRow(myrow);
		        for (int mycol = 0; mycol < xC_TS; mycol++) {
		        	HSSFCell cell = row.createCell(mycol);
		        	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		        	cell.setCellValue(xData[myrow][mycol]);
		        }
		        FileOutputStream fOut = new FileOutputStream(outFile);
		        wb.write(fOut);
		        fOut.flush();
		        fOut.close();
	    	}
		}
	//Change cell type
	public static String cellToString(XSSFCell cell) { 
		// This function will convert an object of type excel cell to a string value
		int type = cell.getCellType();                        
		Object result;                        
		switch (type) {                            
			case XSSFCell.CELL_TYPE_NUMERIC: //0                                
				result = cell.getNumericCellValue();                                
				break;                            
			case XSSFCell.CELL_TYPE_STRING: //1                                
				result = cell.getStringCellValue();                                
				break;                            
			case XSSFCell.CELL_TYPE_FORMULA: //2                                
				throw new RuntimeException("We can't evaluate formulas in Java");  
				case XSSFCell.CELL_TYPE_BLANK: //3                                
				result = "-";                                
				break;                            
			case XSSFCell.CELL_TYPE_BOOLEAN: //4     
				result = cell.getBooleanCellValue();       
				break;                            
			case XSSFCell.CELL_TYPE_ERROR: //5       
				throw new RuntimeException ("This cell has an error");    
			default:                  
				throw new RuntimeException("We don't support this cell type: " + type); 
				}                        
		return result.toString();      
		}
}
